Pro Power Tips 1.0A (c) 1992 Scanlon Enterprises 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴 SPREADSHEET POWER TIPS 컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴컴 Spreadsheets are the bread and butter of business, both large and small. Spread sheets are used to aid the business in calculating financial growth, determine operating expenses and income, and to forecast business opportunity. It is with this in mind, that Spreadsheet Power Tips has been written. Fast Navigation in 1-2-3 Making changes to a massive 1-2-3 worksheet can require you to travel to a distant cell. If you forget where you started out, finding your way back involves time consuming keystrokes. Before you begin a worksheet search , tap the key. Once you find the data you're looking for, hit , and the cursor jumps back to the starting point. Hit once more, and the plus sign disappears. Fast Navigation in 1-2-3 Making changes to a massive 1-2-3 worksheet can require you to travel to a distant cell. If you forget where you started out, finding your way back, involves time consuming keystrokes. Before you begin a worksheet session, tap the key. Once you find the data you're looking for, tap , and the cursor jumps back to the starting point. Tap again, and the plus sign disappears. Highlight Key Data in 1-2-3 If your spreadsheets contain a multitude of individual line items, it's sometimes difficult for co-workers to spot an important bit of information, such as net income. You can use 1- 2-3's range unprotect command to highlight individual cells. Place the cell pointer on the desired cell, issue "/Range Unprotect", and press twice. On a color monitor the cell appears green; a monochrome monitor makes the cell brighter. To Cell and Back with 1-2-3 With 1-2-3's Goto key, you can travel to any part of the worksheet. Press {F5} and use the cursor navigation keys to go to the next spot. To return to your starting point, just press {ESC}. Peruse and Use Faraway 1-2-3 Cells Here's a way to display and use a distant cell of a large 1-2-3 worksheet. At the Ready mode, type a plus sign and the cell address or range name, then press Calc. 1-2-3 will display the current value of the cell on the control panel. Press to store the value in the cell containing the cursor, or press to clear the control panel and return to the Ready mode. Revealing 1-2-3 Formulas To view 1-2-3 formulas, users usually place the cell pointer on a cell and look in the control panel. If you want to see a formula in its cell, simply place the pointer on the cell and issue /RANGE FORMAT TEXT, press {Enter} then {Enter} again. 1-2- 3 displays the formula in that cell. 1-2-3 Copy Controls Cursors 1-2-3 users usually initiate the Copy command with the cell pointer in some cell in the source range. After 1-2-3 copies the range, it automatically returns the pointer. To have the pointer end up in the upper left cell of the target range, put the pointer there before you enter "/Copy" Then press "" or , highlight the source range, and press twice. List 1-2-3 Named Ranges To view a list of named ranges in a 1-2-3 worksheet, press immediately followed by . Then select a range to go to or punch again to see the next five ranges. This technique is also useful when 1-2-3 prompts you to enter a range and you want to specify a previously created name. Find Optimal Solutions With What-If Solver Let's say, you're the treasurer of a pension fund. Your goal, is to invest your members' deposits, and reap the maximum risk. But, each investment presents different interest rates, maturation periods and risk levels, How do you decide where to put the money? You could use the time-honored (but imprecise) method of plugging guesses into data cells until the numbers "looked" right! But there's a better way, What-If Solver, is a 1-2-3 add-in that finds optimal solutions for spreadsheet models. What-If Solver's nonlinear algorithms can handle a much broader range of problems than the linear methods found in Quattro or SuperCalc, and because its intuitive menus shield you from the esoteric of advanced mathematics, What-If Solver is much easier to use. Unlike similar products, What-If Solver works just fine on existing spreadsheet models and the documentation provides a number of clearly described case studies. What-If Solver is available from Frontline Systems, 140 University Ave #100, Palo Alto, CA 94301, (800) 451-0300 ext. 55 or (800) 452-2159 (by fax)... Spruce Up Your Charts With Text and Labels Adding Free-Floating text to Excel charts is easy, but how to do it is not immediately obvious. Just select the chart and start typing. The text will appear in the formula bar. Press , and drag the new text item into place. Choose Format Text to alter its font or colors. If you want to emphasize the texts' subject with an arrow, choose Chart Add Arrow, then click on the end of the arrow and drag it into position. You can also use Excel's default label options. To put a title at the top of your chart, select Chart Attach Text Title, type the appropriate text, and press . Similarly, you can label the x and y axes using Chart Attach Text Category Axes and Chart Text Value Axes, respectively. You can alter an attached label's appearance with Format Text, but if you want to change its position, you must replace it with a Free-Floating label. Getting Weekdays into Your Spreadsheet Sometimes, it is required to list weekdays, and only weekdays, for a given month. Instead of looking at a calendar and manually entering numbers, you can use the following trick that automatically list days of the month, excluding weekends. To use this technique, enter a year value, such as "91" in cell C1. Enter a month (range of 1 to 12) in cell C2. You can use other cells, but you'll have to alter the cell numbers in this method's formula. Now move to cell C4 and enter the following formula: @IF( @MOD( @DATE( $C$1, $C$2,1),7) > 1,1,@IF( @MOD( @DATE( $C$1,$C$2,1),7) = 1,2,2)). Drop down one row to cell C5 and enter @IF( @MOD( @DATE( $C$1, C4 + 1),7) > 1,1,@IF( @MOD( @DATE( $C$1,C4 + 1),7) = 1,2+C4,3 + C4)). Finally, replicate cell C5 to cells C6 through C26. This will display the weekday numbers for the month, which you can use in expressions or simply as row labels. To skip holidays that occur during the week, simply enter the next day number manually. For example, if July 4th falls on a weekday, move to the cell with "4" and enter "5" to replace the formula. Subsequent cells will automatically be updated. Smart Consolidations With Excel 3.0, you can quickly consolidate totals from widely separate sections of a large worksheet or from a group of worksheets. First, open the relevant worksheet(s) and select the range where you want to consolidate the totals. Choose Data Consolidate, which brings up the Consolidate dialog box. Select the first range containing an amount to be aggregated, and click on the Add button in the dialog box. Repeat this process for all the relevant sections or files. Then click OK, or press to consolidate the totals. You can streamline the selection process when you've consolidating date from multiple files. Use similar file names for the relevant files (let's day they all begin with SALES) and the same cell reference for data consolidate, B4:B12 for example. Enter "SALES * .XLS!$B$4:$B$12" in the Reference text box, and Excel will include totals from all worksheets, even un-opened ones, whose names begin with the selected range (SALES example). Speed Up Your Macros With ECHO By default, Excel continuously displays a macro's progress on screen. Lengthy macros operate much faster when you use the ECHO(FALSE) function to stop these time wasting screen updates. Don't use ECHO(FALSE) while debugging a long macro, or you won't know when it hits a snag. When you know a major section of the macro or subroutine is reliable, however, you can surround that section with ECHO(FALSE) at the start and ECHO(TRUE) at the end. When you're though debugging, use Formula Replace to strip out the scattered ECHO statements. Getting the Right Results With Visual Rounding Sometimes, when combining figures from several sections of one spreadsheet, or from linked spreadsheets, the totals are wrong. The problem, seems to be related to rounding. How can this problem be avoided ? Most times, you round numbers, to make them easier to read, but you don't want to change the value of the numbers themselves. In that case, use format commands to limit the number of decimal places the spreadsheet should reveal. With "visual rounding" the numbers look the way you want but keep their exact value. If you use a rounding function, such as @ROUND, in 1-2-3 and Quatro Pro, or =ROUND in Excel, you change a number's value. Think of this as "mathematical rounding". If your report summaries contain formulas which add up tens or hundreds of values, rounded off with ROUND functions, your results can be inaccurate. But if you round all values that contribute to the to the final sum, visually rather than mathematically, your summaries would be on target. To see the difference, set up the following : A B C D 1 2 $57,453.23 $57,453.23 3 $29,492.98 $29,492,98 4 $60,883.33 $60,883.33 5 ======== ======== 6 In 1-2-3 and Quatro Pro, enter @ROUND(@SUM(B2..B4),0) into cell B6. In Excel, use the formula =ROUND(SUM(B2:B4),0). The result is a value rounded mathematically, to the nearest dollar, with no record of whether the number was rounded up or down, or of what happened to the missing decimal places. Now, sum the values in Cells C2 to C4, and display the rounded result without losing track of the change. In 1-2-3 and Quatro Pro, enter @SUM(C2..C4) into cell C6. In Excel, enter =SUM(C2:C4). Then in 1-2-3, select /Range Format Currency, enter 0 and indicate cell C6. In Quatro Pro, indicate cell C6, select Style Numeric Format Currency, enter 0 and click enter. In Excel, indicate cell C6, select Format, Number and apply the format option that reads "$#,##0_);($#,##0). The formatted number is rounded visually, not mathematically. The cell format suppresses the decimal places and rounds to the nearest dollar. Quick Totals for 1-2-3 After you have entered a column of numbers in 1-2-3 or a compatible spreadsheet program, you can use this macro to place a dashed line and an @SUM() function beneath the column quickly and easily. Enter the label "\-{down}@sum ({up2}.{end} {up})~ in an out of the way cell. Use "/Range Name Create" to name the range "\s". To use the macro, place the cell pointer in the cell directly below the column of numbers you want summed, and press -S. Check Behind Lotus Express Selecting any Lotus Express accessory (Reader, Comm_Manager, etc...) pops up a window that obscures whatever else is on the screen. By pressing both {Shift} keys simultaneously, you can make the window temporarily disappear so that you can view the DOS prompt, application, or other Express accessory behind it. Releasing one or both {Shift} keys restores the current pop up window. Streamline Excel Macros With Subroutines Often a macro must repeat the same series of commands over and over. It's easier and more efficient to make those commands a subroutine, a macro within a macro. Here's a simple example. Suppose you want a macro to format several selected columns as percentages. First, use File New Macro sheet, and choose an out of the way area, in this example we'll start at cell A100. To name th subroutine, enter "FormulaPercent()" in cell A100, select Formula Define name, click on Command, and press . Type "=select("c")" in cell A101 and "=format.number("0.00%")" in cell A102. End the subroutine by entering "=return()" in cell A103. Now whenever you want a macro to apply the percentage format to the current column, in your worksheet, simply use the formula "=FormatPercent()" in that macro. Ranking Values With Excel Excel's powerful array feature lets you create a formula to compute ranks, and the ranks will adjust automatically if your numbers change. If the values to be ranked are in A1:A10, for example, type the following formula into cell B1: "=SUM(IF(A1>SAS:SAS10,1))+1" then press {Shift}&{Ctrl}&{Enter} to make this an array formula, indicated by surrounding curly braces. If braces do not appear, press {F2} and try again. Copy the formula to B2:B10, and the ranks will be calculated. With large ranges, however, recalculating these formulas can take a while, so you'll be wise to switch to manual recalc mode. Setting Date Formats in Worksheet Headers and Footers To print the current date in your 1-2-3 or Quattro Pro worksheet, place @ in the header or footer. The format of the printed date is determined by the format you select for the clock display on the status line. Reset the date's format with "/Options|Other|Clock" in Quattro Pro or "/Worksheet|Global | Default|Other|Clock" in 1-2-3. The Last Day of the Month Obtaining the Last Day of the Month in an expression similar to "@END_MONTH (argument)" or the beginning of the month via "@BEG_MONTH (argument)" as a serial date number can be accomplished, in Excel, 1-2-3 and Quatro Pro. In Excel, use the formula "=DATE(YEAR(A2),MONTH(A2),1) to obtain the first day of a month, and "=DATE(YEAR(A2),MONTH(A2)+1,1)-1 for the last day of a month. 'A2' is the cell name containing the serial date, such as 32283 for 2/1491. For 1-2-3 and Quatro, you can use "@DAY(YEAR(A2),MONTH(A2),1)" to get the first day of the month, but, you will have to use a manually constructed lookup table to find the serial number of the last day of the month. Using a manual table method, is NOT as reliable as the Excel formula, so it must contain error trapping to avoid out-of-range dates. To build the table, open a blank worksheet and enter the labels "FIRST", "DATE", "DAYS", "LAST", and "DATE", in cells A1 through E1. In A2 through E2, enter '0', '0', 'ERR', 'ERR' and 'ERR'. In cell A3, enter the formula "@DATE(90,1,1)" (assuming January 1990 is the earliest month you need in the table). Enter "+A3" in cell B3. In cell C3, enter the number of days for the month of the date in A3. In cell D3, enter "+A3+C3-1". In cell E3, enter "+D3". Now copy the formula of B3-E3 to cells B4 - E27 (rows 4 thru 27, columns B thru E), using "/CB3,E3 B4,E27 ". In cell A4, enter "+A3+C3", and enter "/CA4 A5,A27 " to duplicate the formula to cells A5 = A27. Set the cell pointer to cell B3 and enter "RFD4 " to format the entries in column B as "long international" dates. Place the pointer in cell E3 and repeat the date format keystrokes. Locate to cell C4, the number of days in February 1990 and press . Continue entering the appropriate number of days for each month. Finally, in cell C27, D27 & E27 enter 'ERR' and the table 'MONTH' with "\RNCMONTH A1,E27 ". To use the table, enter a serial date (such as 33232) in cell A30, and the formula "@VLOOKUP(A30,MONTH,3)" in cell B30 Section finished. Be sure to order your THREE BONUS DISKS which expand this software package with vital tools, updates and additional tutorial material for computer users! Send $24.95 to Scanlon Enterprises, Department TIP, 38354 17th St. E., Palmdale, CA 93550. Bonus disks shipped promptly! Modifications, custom program versions, Site and LAN licenses of this package for business or corporate use are possible, contact the author. This software is shareware - an honor system which means TRY BEFORE YOU BUY. Press escape key to return to menu.